package com.alen.sun.dao;

import com.alen.sun.bean.CheckIn;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface CheckInMapper {
    //添加
    @Insert("INSERT INTO check_in (check_in_id,name,gender,phoneNum,note,age,address,accepted_or_not," +
            "accept_time,accept_status,folk_phoneNum) VALUES (#{checkInId},#{name},#{gender},#{phoneNum},#{note}," +
            "#{age},#{address},#{acceptedOrNot},#{acceptTime},#{acceptStatus},#{folkPhoneNum})")
    Boolean addCheckIn(CheckIn checkIn);

    //修改
    @Update("UPDATE check_in SET name = #{name}, gender = #{gender},phoneNum = #{phoneNum}," +
            "note = #{note},age = #{age},address = #{address},accepted_or_not = #{acceptedOrNot}," +
            "accept_time = #{acceptTime},accept_status = #{acceptStatus},folk_phoneNum = #{folkPhoneNum} " +
            "WHERE check_in_id = #{checkInId}")
    Boolean updateCheckIn(CheckIn checkIn);

    //删除
    @Delete("DELETE FROM check_in WHERE check_in_id = #{checkInId}")
    Boolean deleteCheckIn(CheckIn checkIn);

    //总记录数
    @Select("select count(*) from check_in")
    Long count();
    //分页查询
    @Select("select * from check_in limit #{start},#{pageSize}")
    List<CheckIn> page(Integer start, Integer pageSize);

    //批量删除
    @Delete({
            "<script>",
            "delete from check_in where check_in_id in",
            "<foreach item='id' collection='ids' open='(' separator=',' close=')'>#{id}</foreach>",
            "</script>"
    })
    Boolean deleteByIds(List<String> ids);

    //查找是否存在记录
    @Select("SELECT count(*) FROM check_in WHERE name = #{name} and phoneNum = #{phoneNum} and folk_phoneNum = #{folkPhoneNum}")
    Integer selectRecord(CheckIn checkIn);

    //查询根据id
    @Select("SELECT count(*) FROM check_in WHERE check_in_id = #{checkInId}")
    Integer selectById(String checkIn);
    
    //网页搜索功能 聚合模糊查询, 姓名,老人电话号码,家属电话号码
    @Select("SELECT * FROM check_in " +
            "WHERE name LIKE CONCAT('%', #{keywords}, '%') " +
            "OR phoneNum LIKE CONCAT('%', #{keywords}, '%') " +
            "OR folk_phoneNum LIKE CONCAT('%', #{keywords}, '%')")
    List<CheckIn> searchByKeywords(String keywords);


    /**
     * 数据分析可视化,获取入住日期及入住数量
     * @return
     */
    @Select("SELECT accept_time, COUNT(*) AS count FROM check_in GROUP BY accept_time ORDER BY accept_time")
    List<Map<String, Object>> getCountByAcceptTime();

    //成功办理入住
    @Update("UPDATE check_in SET accept_status = 1 WHERE check_in_id = #{checkInId}")
    Boolean setStatusByCheckInId(CheckIn checkIn);
}
